<?php
namespace Hilaoyu\Utils;
use Hilaoyu\Utils\Traits\ErrorsTrait;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Shared\Drawing as SharedDrawing;

/**
 * Class UtilExcel
 * @package Hilaoyu\Utils
 * @property Spreadsheet $spreadsheet
 *
 * ////读取
 * $Excel = UtilExcel::read('/xxx.xlsx');
 * //一行一行的读取
 * $Excel->getRow( function ($rowData){
 * //do some thing
 * });
 * //读取整个工作簿
 * $data = $Excel->toArray();
 *
 * ////写入
 * $Excel = UtilExcel::write();
 * $Excel->addHeaderRow(['name'=>'名称','number'=>'学号','image'=>'照片']);
 * $Excel->addRow(['number'=>'1111','name'=>'张三','image'=>'xxx.png']
 * ,null
 * , function ($dataValue,$dataKey,$rowIndex,$columnIndex,$worksheet){
 *  if('image' == $dataKey){
 *      UtilExcel::drawImage($dataValue,$worksheet,$rowIndex,$columnIndex,300,300);
 *      return basename($dataValue);
 *  }
 *  return $dataValue;
 * });
 * //直接下载
 * $Excel->response('Xlsx');
 * //保存为文件
 * $Excel->saveFile('Xlsx','xxx.xlsx');
 */


class UtilExcel {

    use ErrorsTrait;

    protected $spreadsheet = null;
    protected $excel_file = '' ;
    protected $columnDataKeys = [];

    /**
     * 创建一个读取实例
     * @param string $excel_file excel文件绝对路径
     * @return static
     */
    static public function read(string $excel_file){
        return new static($excel_file);
    }

    /**
     * 创建一个写入实例
     * @param string $excel_file
     * @return static
     */
    static public function write($excel_file=''){
        $excel_file = trim($excel_file);
        if(file_exists($excel_file)){
            UtilFile::delDirOrFile($excel_file);
        }
        return new static($excel_file);
    }

    public function __construct($excel_file='')
    {
        $excel_file = trim($excel_file);
        $this->excel_file = $excel_file;
        try{
            if($excel_file && file_exists($excel_file)){
                $reader = IOFactory::createReaderForFile($excel_file);
                $reader->setReadDataOnly(false);
                $this->spreadsheet = $reader->load($excel_file);
            }else{
                $this->spreadsheet = new Spreadsheet();
                $styleArray = [
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_LEFT,
                        'vertical' => Alignment::VERTICAL_CENTER,
                        ],
                    ];
                $this->spreadsheet->getDefaultStyle()->applyFromArray($styleArray);

            }

        }catch (\Exception $exception){
            $this->setError($exception->getMessage());
        }

    }

    public function getSpreadsheet(){
        return $this->spreadsheet;
    }
    /**
     * 返回工作簿总数
     * @return int
     */
    public function getSheetCount(){
        if($this->spreadsheet instanceof Spreadsheet){
            return $this->spreadsheet->getSheetCount();
        }
        return 0 ;
    }

    public function getAllSheets(){
        if($this->spreadsheet instanceof Spreadsheet){
            return $this->spreadsheet->getAllSheets();
        }
        return false;
    }

    /**
     * 返回当前工作簿索引
     * @return bool|int
     */
    public function getActiveSheetIndex(){
        if($this->spreadsheet instanceof Spreadsheet){
            return $this->spreadsheet->getActiveSheetIndex();
        }
        return false ;
    }

    /**
     * 设置活动工作簿
     * @param int $index 工作簿索引
     * @return $this
     */
    public function setActiveSheet($index=0){
        $index = intval($index);
        if($index < 0){
            $index = 0 ;
        }
        if($this->spreadsheet instanceof Spreadsheet){
            $this->spreadsheet->setActiveSheetIndex($index);
        }

        return $this;
    }

    /**
     * 返回活动工作簿实例
     * @return null|\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet
     */
    public function worksheet(){
        if(!$this->spreadsheet instanceof Spreadsheet){
            return null;
        }
        return $this->spreadsheet->getActiveSheet();
    }


    /**
     * 当前工作簿数据总行数
     * @return int
     */
    public function totalRow(){
        if($worksheet = $this->worksheet()){
            return $worksheet->getHighestRow();
        }
        return 0;
    }

    /**
     * 当前工作簿数据总列数
     * @return int
     */
    public function totalColumn(){
        if($worksheet = $this->worksheet()){
            $highestColumnIndex = $worksheet->getHighestColumn();
            return Coordinate::columnIndexFromString($highestColumnIndex);
        }
        return 0;
    }

    /**
     * 光标位置所在行
     * @return int
     */
    public function currentRow(){
        if(!$worksheet = $this->worksheet()){
            return 0;
        }
        $activeCellIndex = $worksheet->getSelectedCells();
        $row = preg_replace('/^.*?([\d]+)$/', '${1}', $activeCellIndex);
        return intval($row);
    }

    /**
     * 光标位置所在列
     * @return int|mixed
     */
    public function currentColumn(){
        if(!$worksheet = $this->worksheet()){
            return 0;
        }
        $activeCellIndex = $worksheet->getSelectedCells();

        $column = preg_replace('/^.*?([A-Z]+)[\d]*$/', '${1}', $activeCellIndex);
        return $column;
    }

    /**
     * 读取一行数据
     * @param \Closure $callback 回调方法 $callback($rowData,$rowIndex,$totalRow,$worksheet);
     * $callback param $rowData array  当前行的数据，键为列名
     * $callback param $rowIndex int  当前行号
     * $callback param $totalRow int  总行数
     * $callback param $worksheet obj  当前工作簿实例
     * $callback return 如果回调返回 false ,则终止读取
     * @param int $startRow 从系第几行开始读，默认为 1
     * @param string $startColumn 从哪一例开始读，默认为 A
     * @return int 读取的总行数
     */
    public function getRow(\Closure $callback,$startRow=1,$startColumn="A"){
        if(! $worksheet = $this->worksheet()){
            return 0;
        }

        //图片，暂不处理
        //$images = $worksheet->getDrawingCollection();
        //dd($this->spreadsheet);

        $startRow = intval($startRow);
        if($startRow < 1){
            $startRow = 1;
        }

        $columnIndexIsString = false;

        if(!is_numeric($startColumn)){
            $columnIndexIsString = true;
            $startColumn = Coordinate::columnIndexFromString($startColumn);
        }

        $startColumn = intval($startColumn);
        if($startColumn < 1){
            $startColumn = 1;
        }

        $totalRow = $this->totalRow();
        $columns = $this->totalColumn();


        $readCount = 0;
        for ($rowIndex = $startRow; $rowIndex <= $totalRow; $rowIndex++) {        //循环读取每个单元格的内容。注意行从1开始，列从1开始
            $rowData = [];
            for ($column = $startColumn; $column <= $columns; $column++) {
                //$cell = $worksheet->getCellByColumnAndRow($column,$rowIndex);
                $columnIndex = $column;
                $columnIndexString = Coordinate::stringFromColumnIndex($column);
                if($columnIndexIsString){
                    $columnIndex = $columnIndexString;
                }

                $cellIndex = $columnIndexString.$rowIndex;
                $cell = $worksheet->getCell($cellIndex);

                //处理合并单元格，合并区域内所有单元格内容取相同
                if($mergeRange = $cell->getMergeRange()){
                    $mergeDataCellIndex = UtilArr::get(explode(':',$mergeRange),0);
                    if($mergeDataCellIndex){
                        $cell = $worksheet->getCell($mergeDataCellIndex);
                    }

                }

                $rowData[$columnIndex] = $cell->getFormattedValue();

                $worksheet->setSelectedCell($cellIndex);
            }

            $readCount++;
            if(false === call_user_func($callback,$rowData,$rowIndex,$totalRow,$worksheet)){
                break;
            }

        }

        return $readCount;
    }

    /**
     * 当前工作所有数据转为数组
     * @return array 二维数组，第一维健为行号，第二维键为列名
     */
    public function toArray(){
        $data = [];
        $this->getRow(function ($rowData,$rowIndex) use (&$data){
            $data[$rowIndex] = $rowData;
        });
        return $data;
    }


    /**
     * 添加一个工作簿，并设置新添加的工作簿为活动工作簿
     * @param null $sheetIndex 工作簿索引，不传则放到最后
     * @param null $pSheet 工作簿对象
     * @param string $name 工作簿名称
     * @return $this
     */
    public function addSheet($sheetIndex = null,Worksheet $pSheet=null,$name=''){
        $name = trim($name);

        if($this->spreadsheet instanceof Spreadsheet){
            if($pSheet){
                $newSheet = $this->spreadsheet->addSheet($pSheet,$sheetIndex);
            }else{
                $newSheet = $this->spreadsheet->createSheet($sheetIndex);
            }

            if($name){
                $newSheet->setTitle($name);
            }
            $this->spreadsheet->setActiveSheetIndex($this->spreadsheet->getIndex($newSheet));

        }

        return $this;
    }

    public function addExternalSheet($sheet){
        if($this->spreadsheet instanceof Spreadsheet){
            $this->spreadsheet->addExternalSheet($sheet);
        }

        return $this;
    }

    public function removeSheetByIndex($pIndex){
        if($this->spreadsheet instanceof Spreadsheet){
            $this->spreadsheet->removeSheetByIndex($pIndex);
        }

        return $this;
    }

    /**
     * 设置 数据键与列的对应关系
     * @param array $columnDataKeys [[data_key1=>column_key1,data_key2=>column_key2,...]]
     * @return $this
     */
    public function setColumnDataKeys(array $columnDataKeys){
        $this->columnDataKeys = $columnDataKeys;
        return $this;
    }

    /**
     * 获取 数据键与列的对应关系
     * @param string $dataKey
     * @return array|mixed
     */
    public function getColumnDataKey($dataKey=''){
        $dataKey = trim($dataKey);
        if($dataKey){
            return UtilArr::get($this->columnDataKeys,$dataKey,null);
        }

        return $this->columnDataKeys;
    }

    /**
     * 清除 数据键与列的对应关系
     * @return $this
     */
    public function clearColumnDataKeys(){
        $this->columnDataKeys = [];
        return $this;
    }
    /**
     * 是否设置了 数据键与列的对应关系
     * @return bool
     */
    public function hasColumnDataKeys(){
        return !empty($this->columnDataKeys);
    }

    /**
     * 添加一个头部行,并以当前数据顺序设置数据键与列的对应关系
     * @param array $rowData
     * @param null $row
     * @return bool
     */
    public function addHeaderRow(array $rowData,$row=null){

        $this->clearColumnDataKeys();
        $data_column_keys = [];
        $status = $this->addRow($rowData,$row,function ($dataValue,$dataKey,$rowIndex,$columnIndex) use (&$data_column_keys){
            $data_column_keys[$dataKey] = $columnIndex;
            return $dataValue;
        });

        $this->setColumnDataKeys($data_column_keys);

        return $status;
    }

    /**
     * 写入一行数据，并将光标移至下一行的A列
     * @param array $rowData 要写入的数据
     * @param null $rowIndex 要写入的行号，默认为当前光标所在行
     * @param \Closure|null $cellCallback 单元格写入前回调 $cellCallback($dataValue,$dataKey,$rowIndex,$columnIndex,$worksheet);
     * $cellCallback param $dataValue 当前要写入单元格的值
     * $cellCallback param $dataKey 当前数据的键
     * $cellCallback param $rowIndex 当前行号
     * $cellCallback param $columnIndex 当前列名
     * $cellCallback param $worksheet 当前工作簿实例
     * $cellCallback return 要写入的值，如果返回为null则不写入,如果返回为false则中止写入
     * @return int  写行总列数
     */
    public function addRow(array $rowData,$rowIndex=null,\Closure $cellCallback=null){
        if(!$worksheet = $this->worksheet()){
            return false;
        }

        if(is_null($rowIndex)){
            $rowIndex = $this->currentRow();
        }

        $rowIndex = intval($rowIndex);
        if($rowIndex < 1){
            $rowIndex = 1;
        }

        $columnIndex = 1;

        $writeCount = 0;
        foreach ($rowData as $dataKey => $dataValue) {

            if($this->hasColumnDataKeys()){
                if(!$columnIndexString = $this->getColumnDataKey($dataKey)){
                    continue;
                }
            }else{
                $columnIndexString = Coordinate::stringFromColumnIndex($columnIndex);
            }

            $cellIndex = $columnIndexString . $rowIndex;
            $worksheet->setSelectedCell($cellIndex);

            if($cellCallback){
                $dataValue = call_user_func($cellCallback,$dataValue,$dataKey,$rowIndex,$columnIndexString,$worksheet);
                if(false===$dataValue){
                    break;
                }
            }

            if(!is_null($dataValue)){
                $worksheet->setCellValue($cellIndex, $dataValue);
            }


            $writeCount++;
            $columnIndex++;
        }


        $worksheet->setSelectedCell('A'.($rowIndex+1));

        return $writeCount;
    }

    /**
     * @param $imagePath 图片文件绝对路径
     * @param Worksheet $worksheet 工作簿实例
     * @param $rowIndex 插入位置 行号
     * @param $columnIndex 插入位置 列名
     * @param int $width 设置图片最大宽度
     * @param int $height 设置图片最大高度
     * @return null|Drawing 绘图对象实例
     */
    static public function drawImage($imagePath,$worksheet,$rowIndex,$columnIndex,$width=100,$height=100){
        if(!file_exists($imagePath)){
            return null;
        }
        $drawing = new Drawing();
        $drawing->setName(UtilFile::basename($imagePath));
        $drawing->setDescription(UtilFile::basename($imagePath));
        //设置图片文件
        $drawing->setPath($imagePath);

        //设置图片宽高
        $width = intval($width);
        if($width <= 0){
            $width = 100;
        }
        $height = intval($height);
        if($height <= 0){
            $height = 100;
        }
        $drawing->setWidth($width);
        if($drawing->getHeight() > $height){
            $drawing->setHeight($height);
        }

        //设置列宽

        $columnWidth = SharedDrawing::pixelsToCellDimension($drawing->getWidth(),$worksheet->getStyle($columnIndex.$rowIndex)->getFont());
        if($worksheet->getColumnDimension($columnIndex)->getWidth() < $columnWidth){
            $worksheet->getColumnDimension($columnIndex)->setWidth($columnWidth);
        }

        //设置行高
        //$rowHeight = SharedDrawing::pixelsToPoints($drawing->getHeight());
        $rowHeight = $drawing->getHeight() * 0.77;
        if($worksheet->getRowDimension($rowIndex)->getRowHeight() < $rowHeight){
            $worksheet->getRowDimension($rowIndex)->setRowHeight($rowHeight);
        }

        //设置图片位置
        $drawing->setCoordinates($columnIndex.$rowIndex);
//        $drawing->setOffsetX(12);
//        $drawing->setOffsetY(12);
        $drawing->setWorksheet($worksheet);
        //dump($worksheet->getDrawingCollection());
        return $drawing;
    }

    /**
     * @param $type 保存文件的类型 Xls|Xlsx|Ods|Csv|Html|Tcpdf|Dompdf|Mpdf
     * @param string $file_path 要保存到的绝对路径
     * @return bool
     */
    public function saveFile($type,$file_path=''){
        $type = UtilStr::ucFirst(trim($type));
        $file_path = trim($file_path);
        if(!$file_path){
            $file_path = $this->excel_file;
        }

        if(!$file_path){
            $this->setError('存储路径错误');
            return false;
        }

        try{
            $writer = IOFactory::createWriter($this->spreadsheet,$type);
            $writer->save($file_path);
            return true;
        }catch (\Exception $exception){
            //dd($exception);
            $this->setError($exception->getMessage());
        }

        return false;

    }

    /**
     * @param $type 下载文件的类型 Xls|Xlsx|Ods|Csv|Html|Tcpdf|Dompdf|Mpdf
     * @param string $fileName 下载文件名，不传则写年月日随机生成
     */
    public function response($type,$fileName=''){
        $fileName = trim($fileName);
        if(''==$fileName){
            $fileExt = '' ;
            switch (strtolower($type)){
                case 'xls':
                    $fileExt = '.xls';
                    break;
                case 'xlsx':
                    $fileExt = '.xlsx';
                    break;
                case 'ods':
                    $fileExt = '.ods';
                    break;
                case 'csv':
                    $fileExt = '.csv';
                    break;
                case 'html':
                    $fileExt = '.html';
                    break;
                case 'tcpdf':
                case 'dompdf':
                case 'mpdf':
                    $fileExt = '.pdf';
                    break;

                default:
                    break;
            }
            $fileName =  uniqid(date('YmdHis_')) . $fileExt;
        }


        @ob_end_clean();//清除缓冲区,避免乱码
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $fileName . '"');
        header("Content-Transfer-Encoding: binary");
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");

        $this->saveFile($type,'php://output');
        exit;
    }


}